<?php
/**
 * Created by PhpStorm.
 * User: Jerry
 * Date: 2016/12/6
 * Time: 18:02
 */

namespace YMApi\Model;


use Think\Model;

/**
 * Class ShelvesModel
 * @package SDXApi\Model
 */
class ShelvesModel extends Model
{
	/**
	 * @var string
	 */
	protected $tableName = 'shelves';

	/**
	 * 获取指定仓库货价商品列表
	 * Created by Jerry
	 * @param int $wid
	 * @return mixed
	 */
	public function get_shelves_service_goods_for_list($wid = 8)
	{
		$sql = "SELECT 
					g.goods_id,
					g.goods_sn,
					g.goods_name,
					s.state,
					g.is_on_sale,
					IFNULL(gs.practical_num,0) as practical_num,
					concat_ws('-', s.shelves_sn, sg.sh_floor) AS shelves_sn 
				FROM
					 sdx_shelves_goods AS sg 
						LEFT JOIN 
                    sdx_shelves AS s ON (sg.sh_id = s.id AND s.wid = {$wid}) 
                        LEFT JOIN 
                    sdx_goods AS g  ON (g.goods_sn = sg.goods_sn) 
                        LEFT JOIN  
                    sdx_goods_storage AS gs ON gs.goods_id=g.goods_id AND gs.wid={$wid} 
				WHERE
    				  g.is_delete = 0
    				  AND is_real = 1";
		$data = $this->query($sql);
		
		return $data;
	}
	
	/**
	 * 获取指定仓库货价商品列表
	 * Created by Jerry
	 * @param int $wid
	 * @return mixed
	 */
	public function get_no_shelves_service_goods_for_list($wid = 8)
	{
	    $sql = "SELECT
	    g.goods_id,
	    g.goods_sn,
	    g.goods_name,
	    s.state,
	    g.is_on_sale,
	    IFNULL(gs.practical_num,0) as practical_num,
	    concat_ws('-', s.shelves_sn, sg.sh_floor) AS shelves_sn
	    FROM
	    sdx_shelves_goods AS sg
	    LEFT JOIN
	    sdx_shelves AS s ON (sg.sh_id = s.id AND s.wid = {$wid})
	    LEFT JOIN
	    sdx_goods AS g  ON (g.goods_sn = sg.goods_sn)
	    LEFT JOIN
	    sdx_goods_storage AS gs ON gs.goods_id=g.goods_id AND gs.wid={$wid}
	    WHERE
	    g.is_delete = 0
	    AND is_real = 1";
	    $data = $this->query($sql);
	
	    return $data;
	}


	/**
	 * 获取商品实际库存
	 * Created by Jerry
	 * @param $goods_id
	 * @return mixed
	 */
	public function get_shelves_service_goods_storage($goods_id)
	{
		$sql = "SELECT 
					SUM(`practical_num`) AS goods_number
				FROM
					sdx_goods_storage
				WHERE
					goods_id = '{$goods_id}'";

		$data = $this->query($sql);
		return $data[0]['goods_number'];
	}
	
	/*
	 * 有库存的商品列表数据
	 * */
	public function get_goods_storage_data($wid=8){
	    $sql = "SELECT g.goods_id,g.goods_sn,g.goods_name,gs.practical_num FROM sdx_goods_storage AS gs". 
               " LEFT JOIN sdx_goods as g ON g.goods_id=gs.goods_id".
               " WHERE gs.wid={$wid} and gs.practical_num>0";
	    $data = $this->query($sql);
	    return $data;
	}
	
	/*
	 * 商品列表数据
	 * */
	public function get_goods_data(){
	    $sql="SELECT  goods_id,goods_sn,goods_name,is_on_sale FROM sdx_goods";
	    $data = $this->query($sql);
	    return $data;
	}

}